using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DTO;

namespace DAO
{
    public class PhongDAO
    {
        private PhongDTO[] ConvertToPhongDTO(DataTable dt)
        {            
            int n = dt.Rows.Count;
            PhongDTO[] Arr = new PhongDTO[n];
            if (n == 0)
                return null;
            for (int i = 0; i < n; i++)
            {
                Arr[i] = new PhongDTO();
                Arr[i].Id_P = Convert.ToInt32(dt.Rows[i]["ID_Phong"].ToString());
                Arr[i].MaP = dt.Rows[i]["MaP"].ToString();
                Arr[i].TenP = dt.Rows[i]["TenP"].ToString();
                Arr[i].MaLoai = Convert.ToInt32(dt.Rows[i]["MaLoai"].ToString());
                Arr[i].GhiChu = dt.Rows[i]["GhiChu"].ToString();
                Arr[i].MaTT = Convert.ToInt32(dt.Rows[i]["MaTinhTrang"].ToString());
            }
            return Arr;
        }
        public PhongDTO[] LayDanhSachPhong()
        {
            PhongDTO[] Arr = new PhongDTO[100];
            try
            {
                DataProvider dp = new DataProvider();
                SqlConnection con = new SqlConnection();
                DataTable dt=new DataTable();
                con = dp.ConnectionData();
                con.Open();

                SqlCommand cmd = con.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_LayDanhSachPhong";

                cmd.ExecuteNonQuery();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
                Arr = ConvertToPhongDTO(dt);

                con.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return Arr;
        }

        public int LapDanhMucPhong(int ID, string MaP, string TenP, int MaLP, string GhiChu, int MaTT)
        {
            PhongDTO pDTO = new PhongDTO();
            pDTO.Id_P = ID;
            pDTO.MaP = MaP;
            pDTO.TenP = TenP;
            pDTO.MaLoai = MaLP;
            pDTO.GhiChu = GhiChu;
            pDTO.MaTT = MaTT;
            ID = LapDanhMucPhongDTO(pDTO);
            return ID;
        }

        private int LapDanhMucPhongDTO(PhongDTO pDTO)
        {
            try
            {
                SqlConnection con = new SqlConnection();
                DataProvider dp = new DataProvider();
                con = dp.ConnectionData();
                con.Open();

                SqlCommand cmd = con.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_ThemPhong";

                cmd.Parameters.Add("@ID", SqlDbType.Int);
                cmd.Parameters.Add("@MaP", SqlDbType.Char);
                cmd.Parameters.Add("@TenP", SqlDbType.NVarChar);
                cmd.Parameters.Add("@MaLoai", SqlDbType.Int);
                cmd.Parameters.Add("@GhiChu", SqlDbType.NText);
                cmd.Parameters.Add("@MaTinhTrang", SqlDbType.Int);

                cmd.Parameters["@ID"].Direction = ParameterDirection.Output;
                cmd.Parameters["@MaP"].Value = pDTO.MaP;
                cmd.Parameters["@TenP"].Value = pDTO.TenP;
                cmd.Parameters["@MaLoai"].Value = pDTO.MaLoai;
                cmd.Parameters["@GhiChu"].Value = pDTO.GhiChu;
                cmd.Parameters["@MaTinhTrang"].Value = pDTO.MaTT;
                cmd.ExecuteNonQuery();

                pDTO.Id_P = Convert.ToInt32(cmd.Parameters["@ID"].Value);
                con.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return pDTO.Id_P;
        }

        private DataTable TimKiemPhongDTO(PhongDTO pDTO)
        {
            DataTable dt = new DataTable();

            try
            {
                string strID = " 1 = 1 ";
                string strMaP = " 1 = 1 ";
                string strTenP = " 1 = 1 ";
                string strLoaiPhong = " 1 = 1 ";
                string strGhiChu = " 1 = 1 ";
                string strTinhTrang = " 1 = 1 ";

                if (pDTO.Id_P != 0)
                    strID = "ID_Phong = " + pDTO.Id_P;
                if (string.Compare(pDTO.MaP, "") != 0)
                    strMaP = "MaP like N'%" + pDTO.MaP + "%'";
                if (string.Compare(pDTO.TenP, "") != 0)
                    strTenP = "TenP like N'%" + pDTO.TenP + "%'";
                if (string.Compare(pDTO.GhiChu, "") != 0)
                    strGhiChu = "GhiChu like N'%" + pDTO.GhiChu + "%'";
                if (pDTO.MaLoai != 0)
                    strLoaiPhong = "MaLoai = " + pDTO.MaLoai;
                if (pDTO.MaTT != 0)
                    strTinhTrang = "MaTinhTrang = " + pDTO.MaTT;

                SqlConnection con = new SqlConnection();
                DataProvider dp = new DataProvider();
                con = dp.ConnectionData();
                con.Open();

                string strSQL = "SELECT * FROM PHONG WHERE ";
                strSQL += strID + " AND ";
                strSQL += strMaP + " AND ";
                strSQL += strTenP + " AND ";
                strSQL += strLoaiPhong + " AND ";
                strSQL += strGhiChu + " AND ";
                strSQL += strTinhTrang;

                SqlCommand cmd = new SqlCommand(strSQL, con);
                cmd.ExecuteNonQuery();

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
                con.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }

            return dt;
        }

        public PhongDTO[] TimKiemPhong(int ID, string MaP, string TenP, int MaLoai, string GhiChu, int TinhTrang)
        {
            PhongDTO[] Arr = new PhongDTO[100];
            try
            {
                PhongDTO pDTO = new PhongDTO();
                pDTO.Id_P = ID;
                pDTO.MaP = MaP;
                pDTO.TenP = TenP;
                pDTO.MaLoai = MaLoai;
                pDTO.GhiChu = GhiChu;
                pDTO.MaTT = TinhTrang;

                DataTable dt = new DataTable();
                dt = TimKiemPhongDTO(pDTO);
                int n = dt.Rows.Count;
                Arr = new PhongDTO[n];
                for (int i = 0; i < n; i++)
                {
                    Arr[i] = new PhongDTO();
                    Arr[i].Id_P = Convert.ToInt32(dt.Rows[i]["ID_Phong"].ToString());
                    Arr[i].MaP = dt.Rows[i]["MaP"].ToString();
                    Arr[i].TenP = dt.Rows[i]["TenP"].ToString();
                    Arr[i].MaLoai = Convert.ToInt32(dt.Rows[i]["MaLoai"].ToString());
                    Arr[i].GhiChu = dt.Rows[i]["GhiChu"].ToString();
                    Arr[i].MaTT = Convert.ToInt32(dt.Rows[i]["MaTinhTrang"].ToString());
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return Arr;
        }

        public void CapNhatPhong(int ID, string MaP, string TenP, int MaLoai, string GhiChu, int MaTT)
        {
            try
            {
                PhongDTO pDTO = new PhongDTO();
                pDTO.Id_P = ID;
                pDTO.MaP = MaP;
                pDTO.TenP = TenP;
                pDTO.MaLoai = MaLoai;
                pDTO.GhiChu = GhiChu;
                pDTO.MaTT = MaTT;
                CapNhatPhongDTO(pDTO);
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }

        private void CapNhatPhongDTO(PhongDTO pDTO)
        {
            try
            {
                SqlConnection con = new SqlConnection();
                DataProvider dp = new DataProvider();
                con = dp.ConnectionData();
                con.Open();

                SqlCommand cmd = con.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_CapNhatPhong";

                cmd.Parameters.Add("@ID", SqlDbType.Int);
                cmd.Parameters.Add("@MaP", SqlDbType.Char);
                cmd.Parameters.Add("@TenP", SqlDbType.NVarChar);
                cmd.Parameters.Add("@MaLoai", SqlDbType.Int);
                cmd.Parameters.Add("@GhiChu", SqlDbType.NText);
                cmd.Parameters.Add("@MaTT", SqlDbType.Int);

                cmd.Parameters["@ID"].Value = pDTO.Id_P;
                cmd.Parameters["@MaP"].Value = pDTO.MaP;
                cmd.Parameters["@TenP"].Value = pDTO.TenP;
                cmd.Parameters["@MaLoai"].Value = pDTO.MaLoai;
                cmd.Parameters["@GhiChu"].Value = pDTO.GhiChu;
                cmd.Parameters["@MaTT"].Value = pDTO.MaTT;

                cmd.ExecuteNonQuery();

                con.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }

        public void XoaPhong(int ID)
        {
            try
            {
                SqlConnection con = new SqlConnection();
                DataProvider dp = new DataProvider();
                con = dp.ConnectionData();
                con.Open();

                SqlCommand cmd = con.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_XoaPhong";

                cmd.Parameters.Add("@ID", SqlDbType.Int);
                cmd.Parameters["@ID"].Value = ID;

                cmd.ExecuteNonQuery();

                con.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }
    }
}
